/*
 * Copyright 2015 William Wang.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package wxk.Bonus.Network;

import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import javafx.application.Platform;
import javafx.concurrent.WorkerStateEvent;
import javafx.event.EventHandler;
import wxk.Bonus.Constant.DBContant;
import wxk.Bonus.utils.BonusLogger;
import wxk.Bonus.utils.Toolkit;
import javafx.scene.control.Label;
import wxk.Bonus.Constant.GlobalConfig;
import wxk.Bonus.database.DBFactory;

/**
 * Jan 28, 201511:23:37 AM
 * @author William Wang
 */
public class UnionLottoStore extends javafx.concurrent.Task{

    BonusLogger mylogger=BonusLogger.getInstance();
static GlobalConfig globalconfig=GlobalConfig.getInstance();
static UnionLottoStore global=null;
   ArrayList result=new ArrayList();

private UnionLottoStore()
{

}
public static UnionLottoStore getInstance()
{
    if(global==null)global=new UnionLottoStore();
    return global;    
}

    @Override
    protected ArrayList call() throws Exception {
      Toolkit tk= new Toolkit();
      mylogger.log(DBContant.URL_UNIONLOTTO);
      globalconfig.showWorking();
      globalconfig.updateSystemStatusBar("Reading from Web "+DBContant.URL_UNIONLOTTO);

      String history_data=tk.readFromWeb(DBContant.URL_UNIONLOTTO);
      //mylogger.log(history_data);
      ArrayList wellformat=tk.parseUnionData1(history_data);
      mylogger.log("wellformat size:"+wellformat.size());
      //clear data
      globalconfig.updateSystemStatusBar("Reading all history "+wellformat.size());
      globalconfig.stopWorking();
        return wellformat;
    }
       int count=0;

 public int initializeDB()
 {
  mylogger.log("call initialzeDB");   
 global.setOnSucceeded( 
        new EventHandler<WorkerStateEvent>() {
    @Override
    public void handle(WorkerStateEvent t) {
       mylogger.log("call initialzeDB handle inner class");   
       UnionLottoSync uls=new UnionLottoSync();
        result = (ArrayList)global.getValue();
        //insert into db
       DBFactory dbfactory = new DBFactory();
         try( Connection conn=dbfactory.getConnection();
             Statement stmt =conn.createStatement();
          )
        {
        long start=System.currentTimeMillis();
        globalconfig.showWorking();
        globalconfig.updateSystemStatusBar("clearing database....");    
        //delete     
        count=uls.clear();
        resetALLHistory(result,conn);
        globalconfig.updateSystemStatusBar("sync data cost time(S):"+(double)((System.currentTimeMillis()-start))/1000);    
        }catch(Exception e)
        {
         e.printStackTrace();
        }
             globalconfig.stopWorking();    
    }});
   return count;
 }
 public  void resetALLHistory(ArrayList<String> his,Connection conn)
		{
       mylogger.log("call resetALLHistory "+(his!=null?his.size():0));   
                    
			StringBuilder sb=new StringBuilder();
			String[]columns;
			sb.append("insert into ").append(DBContant.UNION_TABLE);
			sb.append("(")
			.append("term").append(",")
			.append("day").append(",")
			.append("red1").append(",")
			.append("RED2").append(",")
			.append("RED3").append(",")
			.append("RED4").append(",")
			.append("RED5").append(",")
			.append("RED6").append(",")
			.append("BLUE").append(")");
			StringBuilder sql=new StringBuilder();
			for(int i=0;i<his.size();i++)
			{
			 String line=his.get(i);
			 columns = line.split(" ");
			 sql.append(" select ");
			    //add data
			 sql.append(columns[0]).append(",'")//term
				  .append(columns[1]).append("',")//date
				  .append(columns[2]).append(",")//red1
				  .append(columns[3]).append(",")//red2
				  .append(columns[4]).append(",")//red3
				  .append(columns[5]).append(",")//red4
				  .append(columns[6]).append(",")//red5
				  .append(columns[7]).append(",")//red6
				  .append(columns[8]);//blue
				//
				if(i%DBContant.BULK_INSERT_LINES !=0 && i< (his.size()-1) )
				{
globalconfig.updateSystemStatusBar("Processing "+i+" records...");                                   
					sql.append(" \n union ");
				}
			 if(i%DBContant.BULK_INSERT_LINES==0 )
			 {
globalconfig.updateSystemStatusBar("Insert to Database...");                                   
                             try(
                                Statement stmt =conn.createStatement();
                          )  {
                            stmt.executeUpdate(sb.toString()+sql.toString());
			  sql.setLength(0);//clear string
                               }catch(Exception e)
                               {
                                   e.printStackTrace();
                               }
			 }
			 
			}
			if(sql.length()>0)
			{
                         try(
                                Statement stmt =conn.createStatement();
                          )  {
                            stmt.executeUpdate(sb.toString()+sql.toString());
			  sql.setLength(0);//clear string
                               }catch(Exception e)
                               {
                                   e.printStackTrace();
                               }
			}
		}
}
